# Oracle Environment Check Script
# Used for diagnosing Oracle connection issues

param(
    [string]$OracleHost = "10.3.255.250",
    [int]$OraclePort = 15521,
    [string]$ServiceName = "HISDB"
)

Write-Host "=== Oracle Environment Diagnostic Tool ===" -ForegroundColor Green
Write-Host "Check Time: $(Get-Date)" -ForegroundColor Gray
Write-Host ""

# 1. Network Connectivity Check
Write-Host "1. Network Connectivity Check" -ForegroundColor Yellow
Write-Host "Target Server: ${OracleHost}:${OraclePort}"

# Ping Test
Write-Host "   - Ping Test..." -NoNewline
try {
    $pingResult = Test-Connection -ComputerName $OracleHost -Count 2 -Quiet
    if ($pingResult) {
        Write-Host " [SUCCESS]" -ForegroundColor Green
    } else {
        Write-Host " [FAILED]" -ForegroundColor Red
        Write-Host "     Warning: Cannot ping Oracle server" -ForegroundColor Red
    }
} catch {
    Write-Host " [ERROR]" -ForegroundColor Red
    Write-Host "     Error: $($_.Exception.Message)" -ForegroundColor Red
}

# TCP Port Test
Write-Host "   - TCP Port Test..." -NoNewline
try {
    $tcpClient = New-Object System.Net.Sockets.TcpClient
    $connect = $tcpClient.BeginConnect($OracleHost, $OraclePort, $null, $null)
    $wait = $connect.AsyncWaitHandle.WaitOne(5000, $false)
    
    if ($wait) {
        try {
            $tcpClient.EndConnect($connect)
            Write-Host " [SUCCESS]" -ForegroundColor Green
            $tcpClient.Close()
        } catch {
            Write-Host " [FAILED]" -ForegroundColor Red
            Write-Host "     Error: Port $OraclePort cannot be connected" -ForegroundColor Red
        }
    } else {
        Write-Host " [TIMEOUT]" -ForegroundColor Red
        Write-Host "     Error: Connection timeout" -ForegroundColor Red
    }
} catch {
    Write-Host " [ERROR]" -ForegroundColor Red
    Write-Host "     Error: $($_.Exception.Message)" -ForegroundColor Red
} finally {
    if ($tcpClient) { $tcpClient.Close() }
}

Write-Host ""

# 2. Oracle Client Check
Write-Host "2. Oracle Client Check" -ForegroundColor Yellow

# Check Oracle.ManagedDataAccess.dll
Write-Host "   - Oracle.ManagedDataAccess Assembly..." -NoNewline
try {
    $assembly = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.ManagedDataAccess")
    if ($assembly) {
        Write-Host " [LOADED]" -ForegroundColor Green
        Write-Host "     Version: $($assembly.GetName().Version)" -ForegroundColor Gray
        Write-Host "     Location: $($assembly.Location)" -ForegroundColor Gray
    } else {
        Write-Host " [NOT FOUND]" -ForegroundColor Red
    }
} catch {
    Write-Host " [LOAD FAILED]" -ForegroundColor Red
    Write-Host "     Error: $($_.Exception.Message)" -ForegroundColor Red
}

# Check Oracle Environment Variables
Write-Host "   - Oracle Environment Variables..." -NoNewline
$oracleHome = $env:ORACLE_HOME
$oracleSid = $env:ORACLE_SID
$tnsAdmin = $env:TNS_ADMIN
$path = $env:PATH

if ($oracleHome -or $oracleSid -or $tnsAdmin -or ($path -like "*Oracle*")) {
    Write-Host " [FOUND]" -ForegroundColor Green
    if ($oracleHome) { Write-Host "     ORACLE_HOME: $oracleHome" -ForegroundColor Gray }
    if ($oracleSid) { Write-Host "     ORACLE_SID: $oracleSid" -ForegroundColor Gray }
    if ($tnsAdmin) { Write-Host "     TNS_ADMIN: $tnsAdmin" -ForegroundColor Gray }
} else {
    Write-Host " [NOT SET]" -ForegroundColor Yellow
    Write-Host "     Note: Oracle.ManagedDataAccess does not require Oracle client" -ForegroundColor Gray
}

Write-Host ""

# 3. .NET Runtime Check
Write-Host "3. .NET Runtime Check" -ForegroundColor Yellow

# Check .NET Version
Write-Host "   - .NET Version..." -NoNewline
try {
    $dotnetVersion = dotnet --version 2>$null
    if ($dotnetVersion) {
        Write-Host " [INSTALLED]" -ForegroundColor Green
        Write-Host "     Version: $dotnetVersion" -ForegroundColor Gray
    } else {
        Write-Host " [NOT FOUND]" -ForegroundColor Red
    }
} catch {
    Write-Host " [CHECK FAILED]" -ForegroundColor Red
}

Write-Host ""

# 4. Test Oracle Connection String
Write-Host "4. Oracle Connection Test" -ForegroundColor Yellow
$connectionString = "Data Source=${OracleHost}:${OraclePort}/${ServiceName};User Id=his_bi;Password=his_bi;"
Write-Host "   Connection String: Data Source=${OracleHost}:${OraclePort}/${ServiceName};User Id=his_bi;Password=***;" -ForegroundColor Gray

Write-Host "   - Connection Test..." -NoNewline
try {
    # Try to load Oracle.ManagedDataAccess
    Add-Type -Path "Oracle.ManagedDataAccess.dll" -ErrorAction SilentlyContinue
    
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.Open()
    
    Write-Host " [SUCCESS]" -ForegroundColor Green
    Write-Host "     Server Version: $($connection.ServerVersion)" -ForegroundColor Gray
    Write-Host "     Database Name: $($connection.DatabaseName)" -ForegroundColor Gray
    
    # Simple Query Test
    $command = $connection.CreateCommand()
    $command.CommandText = "SELECT SYSDATE FROM DUAL"
    $result = $command.ExecuteScalar()
    Write-Host "     Query Test: Success (Current Time: $result)" -ForegroundColor Gray
    
    $connection.Close()
} catch {
    Write-Host " [FAILED]" -ForegroundColor Red
    Write-Host "     Error: $($_.Exception.Message)" -ForegroundColor Red
    
    # Analyze Error Type
    $errorMessage = $_.Exception.Message
    if ($errorMessage -like "*ORA-50201*") {
        Write-Host "     Analysis: Oracle communication failed, possible network issue or connection string error" -ForegroundColor Yellow
    } elseif ($errorMessage -like "*ORA-01017*") {
        Write-Host "     Analysis: Invalid username or password" -ForegroundColor Yellow
    } elseif ($errorMessage -like "*ORA-12154*") {
        Write-Host "     Analysis: TNS resolution error, check connection string format" -ForegroundColor Yellow
    } elseif ($errorMessage -like "*timeout*") {
        Write-Host "     Analysis: Connection timeout, check network and firewall settings" -ForegroundColor Yellow
    } elseif ($errorMessage -like "*could not load*" -or $errorMessage -like "*assembly*") {
        Write-Host "     Analysis: Oracle.ManagedDataAccess assembly load failed" -ForegroundColor Yellow
    }
}

Write-Host ""

# 5. System Information
Write-Host "5. System Information" -ForegroundColor Yellow
Write-Host "   - OS: $((Get-WmiObject Win32_OperatingSystem).Caption)" -ForegroundColor Gray
Write-Host "   - Architecture: $($env:PROCESSOR_ARCHITECTURE)" -ForegroundColor Gray
Write-Host "   - PowerShell Version: $($PSVersionTable.PSVersion)" -ForegroundColor Gray
Write-Host "   - Current User: $($env:USERNAME)" -ForegroundColor Gray
Write-Host "   - Computer Name: $($env:COMPUTERNAME)" -ForegroundColor Gray

Write-Host ""

# 6. Troubleshooting Suggestions
Write-Host "6. Troubleshooting Suggestions" -ForegroundColor Yellow
Write-Host "   If connection fails, please check the following:" -ForegroundColor Gray
Write-Host "   1. Confirm Oracle server address and port: ${OracleHost}:${OraclePort}" -ForegroundColor Gray
Write-Host "   2. Confirm network connectivity (firewall, network policy)" -ForegroundColor Gray
Write-Host "   3. Confirm Oracle service is running" -ForegroundColor Gray
Write-Host "   4. Confirm username and password are correct" -ForegroundColor Gray
Write-Host "   5. Check if Oracle.ManagedDataAccess.dll is properly deployed" -ForegroundColor Gray
Write-Host "   6. If using Oracle client, confirm version compatibility" -ForegroundColor Gray

Write-Host ""
Write-Host "=== Diagnosis Complete ===" -ForegroundColor Green
Write-Host "For more detailed testing, please run the application's diagnostic API" -ForegroundColor Gray